﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.OleDb;
using TdgMaker.DataType;
using System.Collections;
using System.Windows.Forms;

namespace TdgMaker
{
    class ExlReader
    {
        public string xls_Name = ".\\TB.xls";
        public string sheet_Name = "Balise telegram$";
        ArrayList TrackList = new ArrayList();



        public void ImportTrackDataUpLine()
        {
            try
            {
                //创建一个数据链接 
                string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + xls_Name + ";Extended Properties =\"Excel 8.0;HDR=no;IMEX=1;\"";
                OleDbConnection myConn = new OleDbConnection(strCon);
                string strCom = " SELECT * FROM [" + sheet_Name + "] ";
                myConn.Open();
                //打开数据链接
                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, myConn);

                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                int TrackLoc_index = 0;
                int Type_index = 0;
                int Length_index = 0;
                int CF_index = 0;
                int StationName_index = 0;
                int TrackName_index = 0;
                while (myDataReader.Read())
                {
                    if (TrackLoc_index == 0 || Type_index == 0 || Length_index == 0 || CF_index == 0 || StationName_index == 0 || TrackName_index == 0)
                    {
                        for (int i = 0; i < myDataReader.FieldCount; i++)
                        {
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("信号点里程") > -1)
                            {
                                TrackLoc_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("区段属性") > -1)
                            {
                                Type_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("长度") > -1)
                            {
                                Length_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("载频") > -1)
                            {
                                CF_index = i;
                                TrackName_index = i - 1;
                                Length_index = i + 1;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("车站名") > -1)
                            {
                                StationName_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("轨道名称") > -1)
                            {
                                TrackName_index = i;
                            }
                            //System.Console.WriteLine(Convert.ToString(myDataReader.GetValue(i)));
                        }
                    }
                    else
                    {

                        TrackData td = new TrackData();
                        td.CF = Convert.ToString(myDataReader.GetValue(CF_index));
                        if (td.CF.Length > 0)
                        {
                            td.Length = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(Length_index)), 10);
                            td.Type = Convert.ToString(myDataReader.GetValue(Type_index));
                            td.SetTrackLoc(Convert.ToString(myDataReader.GetValue(TrackLoc_index)));
                            td.SignalName = (Convert.ToString(myDataReader.GetValue(TrackLoc_index - 1)));

                            td.SetSNameTName(Convert.ToString(myDataReader.GetValue(StationName_index)), Convert.ToString(myDataReader.GetValue(TrackName_index)));
                            TB_DB.adNewTrackDataUpLine(td);
                        }
                    }

                }

                //关闭此数据链接 
                myConn.Close();
            }
            catch
            { }
        }

        public void ImportTrackDataDownLine()
        {
            try
            {
                //创建一个数据链接 
                string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + xls_Name + ";Extended Properties =\"Excel 8.0;HDR=no;IMEX=1;\"";

                OleDbConnection myConn = new OleDbConnection(strCon);
                string strCom = " SELECT * FROM [" + sheet_Name + "] ";
                myConn.Open();
                //打开数据链接
                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, myConn);

                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                int TrackLoc_index = 0;
                int Type_index = 0;
                int Length_index = 0;
                int CF_index = 0;
                int StationName_index = 2;
                int TrackName_index = 0;
                while (myDataReader.Read())
                {
                    if (TrackLoc_index == 0 || Type_index == 0 || Length_index == 0 || CF_index == 0 || StationName_index == 0 || TrackName_index == 0)
                    {
                        for (int i = 0; i < myDataReader.FieldCount; i++)
                        {
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("信号点里程") > -1)
                            {
                                TrackLoc_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("区段属性") > -1)
                            {
                                Type_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("长度") > -1)
                            {
                                Length_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("载频") > -1)
                            {
                                CF_index = i;
                                TrackName_index = i - 1;
                                Length_index = i + 1;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("车站名") > -1)
                            {
                                StationName_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("轨道名称") > -1)
                            {
                                TrackName_index = i;
                            }

                        }
                    }
                    else
                    {

                        TrackData td = new TrackData();
                        td.CF = Convert.ToString(myDataReader.GetValue(CF_index));
                        if (td.CF.Length > 0)
                        {
                            td.Length = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(Length_index)), 10);
                            td.Type = Convert.ToString(myDataReader.GetValue(Type_index));
                            td.SetTrackLoc(Convert.ToString(myDataReader.GetValue(TrackLoc_index)));
                            td.SignalName = (Convert.ToString(myDataReader.GetValue(TrackLoc_index - 1)));
                            td.SetSNameTName(Convert.ToString(myDataReader.GetValue(StationName_index)), Convert.ToString(myDataReader.GetValue(TrackName_index)));
                            TB_DB.adNewTrackDataDownLine(td);
                        }
                    }

                }

                //关闭此数据链接 
                myConn.Close();
            }
            catch
            { }
        }

        public void ImportRoute(string name)
        {
            string x = System.IO.Path.GetFileNameWithoutExtension(name);
            string stationName = StationName.TranName(x);
            try
            {
                //创建一个数据链接 
                string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + name + ";Extended Properties =\"Excel 8.0;HDR=no;IMEX=1;\"";
                OleDbConnection myConn = new OleDbConnection(strCon);

                myConn.Open();
                DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow dr in dt.Rows)
                {
                    //System.Console.WriteLine((String)dr["TABLE_NAME"]);
                    if (((String)dr["TABLE_NAME"]).Substring(((String)dr["TABLE_NAME"]).Length - 1, 1) == "$" || ((String)dr["TABLE_NAME"]).Substring(((String)dr["TABLE_NAME"]).Length - 2, 2) == "$'")
                    {
                        try
                        {
                            System.Console.WriteLine((String)dr["TABLE_NAME"]);

                            string strCom = " SELECT * FROM [" + (String)dr["TABLE_NAME"] + "] ";

                            //打开数据链接
                            OleDbCommand myOleDbCommand = new OleDbCommand(strCom, myConn);

                            OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                            int RouteNr_index = 0;
                            int RouteType_index = 0;
                            int BaliseNr_index = 0;
                            int PassBalise_index = 0;
                            int PassSwith_index = 0;
                            int Speeds_index = 0;
                            int PassTracks_index = 0;
                            int StartSignal = 0;
                            int EndSignal = 0;
                            while (myDataReader.Read())
                            {
                                if (RouteNr_index == 0 || RouteType_index == 0 || BaliseNr_index == 0 || PassBalise_index == 0 || PassSwith_index == 0 || PassTracks_index == 0)
                                {
                                    for (int i = 0; i < myDataReader.FieldCount; i++)
                                    {
                                        if (Convert.ToString(myDataReader.GetValue(i)).Replace(" ", "").IndexOf("联锁进路编号") > -1)
                                        {
                                            RouteNr_index = i;
                                        }
                                        if (Convert.ToString(myDataReader.GetValue(i)).Replace(" ", "").IndexOf("进路类型") > -1)
                                        {
                                            RouteType_index = i;
                                            StartSignal = i + 1;
                                            EndSignal = i + 3;

                                        }
                                        if (Convert.ToString(myDataReader.GetValue(i)).Replace(" ", "").IndexOf("应答器编号") > -1)
                                        {
                                            BaliseNr_index = i;
                                        }
                                        if (Convert.ToString(myDataReader.GetValue(i)).Replace(" ", "").IndexOf("应答器组单元编号/链接距离") > -1)
                                        {
                                            PassBalise_index = i;
                                        }
                                        if (Convert.ToString(myDataReader.GetValue(i)).Replace(" ", "").IndexOf("道岔") > -1)
                                        {
                                            PassSwith_index = i;
                                        }
                                        if (Convert.ToString(myDataReader.GetValue(i)).Replace(" ", "").IndexOf("线路速度") > -1)
                                        {
                                            Speeds_index = i;
                                        }
                                        if (Convert.ToString(myDataReader.GetValue(i)).Replace(" ", "").IndexOf("轨道区段") > -1)
                                        {
                                            PassTracks_index = i;
                                        }

                                    }
                                }
                                else
                                {

                                    RouteData rd = new RouteData();
                                    rd.IL_Nr = Convert.ToString(myDataReader.GetValue(RouteNr_index));
                                    if (rd.IL_Nr != "")
                                    {
                                        rd.SName_RouteNr = stationName + "@" + Convert.ToString(myDataReader.GetValue(RouteNr_index));
                                        rd.RouteType = Convert.ToString(myDataReader.GetValue(RouteType_index));
                                        rd.BaliseNr = Convert.ToString(myDataReader.GetValue(BaliseNr_index));
                                        rd.PassBalise = Convert.ToString(myDataReader.GetValue(PassBalise_index));
                                        rd.PassSwith = Convert.ToString(myDataReader.GetValue(PassSwith_index));
                                        rd.Speeds = Convert.ToString(myDataReader.GetValue(Speeds_index));
                                        rd.PassTracks = Convert.ToString(myDataReader.GetValue(PassTracks_index));
                                        rd.sSignal = Convert.ToString(myDataReader.GetValue(StartSignal));
                                        rd.eSignal = Convert.ToString(myDataReader.GetValue(EndSignal));
                                        
                                        TB_DB.adNewRoute(rd);
                                    }
                                }

                            }
                            // MessageBox.Show((String)dr["TABLE_NAME"]+"done");
                        }
                        catch { }
                    }

                }

                //关闭此数据链接 
                myConn.Close();
            }
            catch { }
        }

        public void ImportBalisePos(int updown)
        {
            try
            {
                TrackList = TB_DB.GetTrackListForBalisePos(updown);
                //创建一个数据链接 
                string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + xls_Name + ";Extended Properties =\"Excel 8.0;HDR=no;IMEX=1;\"";
                OleDbConnection myConn = new OleDbConnection(strCon);
                string strCom = " SELECT * FROM [" + sheet_Name + "] ";
                myConn.Open();
                //打开数据链接
                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, myConn);

                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                int BaliseName_index = 0;
                int BaliseNr_index = 0;
                int BaliseType_index = 0;
                int BaliseLoc_index = 0;
                while (myDataReader.Read())
                {
                    if (BaliseNr_index == 0 || BaliseType_index == 0 || BaliseLoc_index == 0)
                    {
                        for (int i = 0; i < myDataReader.FieldCount; i++)
                        {
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("应答器名称") > -1)
                            {
                                BaliseName_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("应答器编号") > -1)
                            {
                                BaliseNr_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("设备类型") > -1)
                            {
                                BaliseType_index = i;
                            }
                            if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("里程") > -1)
                            {
                                BaliseLoc_index = i;
                            }
                        }
                    }
                    else
                    {

                        BalisePos bp = new BalisePos();
                        if (Convert.ToString(myDataReader.GetValue(BaliseLoc_index)).Length > 2)
                        {
                            bp.Up_Down = updown;
                            bp.Balise_Name = Convert.ToString(myDataReader.GetValue(BaliseName_index));
                            bp.Balise_Type = Convert.ToString(myDataReader.GetValue(BaliseType_index));
                            bp.SetBaliseNr(Convert.ToString(myDataReader.GetValue(BaliseNr_index)));
                            bp.SetBalsie_Loc(Convert.ToString(myDataReader.GetValue(BaliseLoc_index)));
                            bp.Lateral_Line = Convert.ToString(myDataReader.GetValue(BaliseType_index + 2));
                            if (bp.Lateral_Line.IndexOf("侧线") > -1)
                            {
                            }
                            else
                            {
                                for (int i = 0; i < TrackList.Count; i++)
                                {
                                    TrackData td = (TrackData)TrackList[i];

                                    if (i < TrackList.Count - 1)
                                    {
                                        TrackData td1 = (TrackData)TrackList[i + 1];
                                        if (isInclude(td.TrackLoc, td1.TrackLoc, bp.Balise_Loc))
                                        {
                                            bp.SNameTName = td.SNameTName;
                                            bp.Track_Offset = cal(td.TrackLoc, td1.TrackLoc, bp.Balise_Loc, td.Length);
                                            //    System.Console.WriteLine(string.Format("{0}--{1}--{2}--{3}--{4}", bp.Balise_Loc, td.TrackLoc, bp.Track_Offset, td.Length, td.SNameTName));
                                        }
                                    }
                                    else
                                    {
                                        if (isInclude(td.TrackLoc, td.TrackLoc + td.Length, bp.Balise_Loc))
                                        {
                                            bp.SNameTName = td.SNameTName;
                                            bp.Track_Offset = cal(td.TrackLoc, td.TrackLoc + td.Length, bp.Balise_Loc, td.Length);
                                            //      System.Console.WriteLine(string.Format("{0}--{1}--{2}--{3}--{4}", bp.Balise_Loc, td.TrackLoc, bp.Track_Offset, td.Length, td.SNameTName));
                                        }
                                    }

                                }
                            }
                            TB_DB.adNewBalisePos(bp);
                        }
                    }

                }

                //关闭此数据链接 
                myConn.Close();
            }
            catch
            { }
        }

        public void ImportBaliseTelegram()
        {
            try
            {//创建一个数据链接 
                string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + xls_Name + ";Extended Properties =\"Excel 8.0;HDR=no;IMEX=1;\"";
                OleDbConnection myConn = new OleDbConnection(strCon);

                myConn.Open();
                DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow dr in dt.Rows)
                {
                    System.Console.WriteLine((String)dr["TABLE_NAME"]);
                    // if (((String)dr["TABLE_NAME"]).Substring(((String)dr["TABLE_NAME"]).Length - 1, 1) == "$")
                    {
                        try
                        {
                            System.Console.WriteLine((String)dr["TABLE_NAME"]);

                            string strCom = " SELECT * FROM [" + (String)dr["TABLE_NAME"] + "] ";

                            //打开数据链接
                            OleDbCommand myOleDbCommand = new OleDbCommand(strCom, myConn);

                            OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                            int BaliseNr_index = 0;
                            int BaliseVer_index = 0;
                            int BaliseTele_index = 0;
                            while (myDataReader.Read())
                            {
                                if (BaliseNr_index == 0 || BaliseVer_index == 0 || BaliseTele_index == 0)
                                {
                                    for (int i = 0; i < myDataReader.FieldCount; i++)
                                    {
                                        if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("应答器编号") > -1)
                                        {
                                            BaliseNr_index = i;
                                        }
                                        if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("版本号") > -1)
                                        {
                                            BaliseVer_index = i;
                                        }
                                        if (Convert.ToString(myDataReader.GetValue(i)).IndexOf("用户报文") > -1)
                                        {
                                            BaliseTele_index = i;
                                        }

                                    }
                                }
                                else
                                {

                                    BaliseTele bt = new BaliseTele();
                                    bt.Tele = Convert.ToString(myDataReader.GetValue(BaliseTele_index));
                                    if (bt.Tele.Length > 20)
                                    {
                                        bt.SetBaliseNr(Convert.ToString(myDataReader.GetValue(BaliseNr_index)));
                                        bt.Tele_Version = Convert.ToString(myDataReader.GetValue(BaliseVer_index));
                                        TB_DB.adNewBaliseTele(bt);
                                    }
                                }

                            }
                            // MessageBox.Show((String)dr["TABLE_NAME"]+"done");
                        }
                        catch { }

                    }
                }
                //关闭此数据链接 
                myConn.Close();
            }
            catch { }

        }

        private int cal(int startPos, int endPos, int balisePos, int length)
        {
            //自己都晕，写个注释，以此留念
            //所有轨道列表都按列车正向运行排序

            //如果开始点小于结束点，说明是下行正向
            if (startPos < endPos)
            {
                //如果应答器点离开试点比较近，就正正常计算应答器与绝缘节的偏差
                if (balisePos - (startPos + endPos) / 2 <= 0)
                {
                    return balisePos - startPos;
                }
                //如果离结束点比较近，结束点-应答器点，然后轨道长度再减去 应答器离结束点的距离，等于应答器离开试点的位置
                else
                {
                    return length - (endPos - balisePos);
                }
            }
            //否则上行正向
            else
            {
                //如果应答器点离开试点比较近，就正正常计算应答器与绝缘节的偏差
                if (balisePos - (startPos + endPos) / 2 >= 0)
                {
                    return startPos - balisePos;
                }
                //如果离结束点比较近，结束点-应答器点，然后轨道长度再减去 应答器离结束点的距离，等于应答器离开试点的位置
                else
                {
                    return length - (balisePos - endPos);
                }
            }
        }

        private bool isInclude(int startPos, int endPos, int balisePos)
        {
            if (startPos < endPos)
            {
                if (balisePos > startPos && balisePos < endPos)
                    return true;
                else
                    return false;
            }
            else
            {
                if (balisePos > endPos && balisePos < startPos)
                    return true;
                else
                    return false;
            }
        }
    }
}
